Google BigQuery: データセットの一覧情報を取得
小ネタです。
Google BigQueryのデータセットでは、管理下にあるテーブル群に関連する情報等がそれぞれ存在します。それらのデータを一覧参照出来る情報が欲しいなと思い、そのためにはどういう方法が使えるのかを確認し、まとめてみました。
やりたいこと、欲しいもの
「データセット」に関する一覧情報を表示させたい。そのために必要なデータセットにおける列情報を必要な分、何らかの形で取得したい。概ね以下の画面で得られるものが該当。
Pythonプログラムを介してデータセット情報を取得
公式ドキュメントには以下のテーマで展開されている情報があります。
その中で、プログラム言語による情報の取得方法が紹介されていたので試してみました。任意のデータセットIDを設定すれば取得出来る流れになっています。ただ、既存参照コードのままだと得られる情報が少なかったので、途中確認用コードを数行追加してみました。
# TODO(developer): Import the client library.
from google.cloud import bigquery
# TODO(developer): Construct a BigQuery client object.
client = bigquery.Client()
# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
dataset_id = 'cm-xxxxxxxxxxxxxx.cmbqdataset'
dataset = client.get_dataset(dataset_id) # Make an API request.
print("==================")
print(dir(dataset))
print("-----------")
print(type(dataset))
print(vars(dataset))
print("===================")
full_dataset_id = "{}.{}".format(dataset.project, dataset.dataset_id)
friendly_name = dataset.friendly_name
print(
"Got dataset '{}' with friendly_name '{}'.".format(
full_dataset_id, friendly_name
)
)
# View dataset properties.
print("Description: {}".format(dataset.description))
print("Labels:")
labels = dataset.labels
if labels:
for label, value in labels.items():
print("\t{}: {}".format(label, value))
else:
print("\tDataset has no labels defined.")
# View tables in dataset.
print("Tables:")
tables = list(client.list_tables(dataset)) # Make an API request(s).
if tables:
for table in tables:
print("\t{}".format(table.table_id))
else:
print("\tThis dataset does not contain any tables.")
プログラム実行結果。一部見易さを考慮して改行を加えています。データセット単位で必要な情報は揃ってそうな感じです。
$ python view_bq_dataset.py
==================
['_PROPERTY_TO_API_FIELD', '__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_build_resource', '_properties', 'access_entries', 'created', 'dataset_id', 'default_encryption_configuration', 'default_partition_expiration_ms', 'default_table_expiration_ms', 'description', 'etag', 'friendly_name', 'from_api_repr', 'from_string', 'full_dataset_id', 'labels', 'location', 'model', 'modified', 'path', 'project', 'reference', 'routine', 'self_link', 'table', 'to_api_repr']
-----------
<class 'google.cloud.bigquery.dataset.Dataset'>
{'_properties': {
'kind': 'bigquery#dataset',
'etag': 'LFRTXxxXXxxXXXXXXXX==',
'id': 'cm-xxxxxxxxxxxxxx:cmbqdataset',
'selfLink': 'https://bigquery.googleapis.com/bigquery/v2/projects/cm-xxxxxxxxxxxxxx/datasets/cmbqdataset',
'datasetReference': {
'datasetId': 'cmbqdataset',
'projectId': 'cm-xxxxxxxxxxxxxx'
},
'description': 'テスト：ブログ用に作成したBigQueryデータセットです。',
'defaultTableExpirationMs': '15552000000',
'labels': {
'env': 'develop',
'production': 'false'
},
'access': [
{'role': 'WRITER', 'specialGroup': 'projectWriters'},
{'role': 'OWNER', 'specialGroup': 'projectOwners'},
{'role': 'OWNER', 'userByEmail': 'xxxxxxxxxxxxx@yyyyyyyyyyyyyyyy'},
{'role': 'READER', 'specialGroup': 'projectReaders'}
],
'creationTime': '1582620145008',
'lastModifiedTime': '1583965989780',
'location': 'asia-northeast1'
}}
===================
Got dataset 'cm-xxxxxxxxxxxxxx.cmbqdataset' with friendly_name 'None'.
Description: テスト：ブログ用に作成したBigQueryデータセットです。
Labels:
env: develop
production: false
Tables:
sample_table_1
t_sales
INFORMATION_SCHEMAの内容を活用する
冒頭参照先として挙げたドキュメントには、現状ベータ版扱いではありますがBigQueryでデータセット、ルーティン、テーブル、ビューに関するメタデータへのアクセスを提供する一連のビューとして用意されているINFORMATION_SCHEMAを活用する方法が載っています。内容を見る限り、これらの情報をを使うのが一番手っ取り早く且つ効率的です。
$ bq query --nouse_legacy_sql ' SELECT * EXCEPT(schema_owner) FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY catalog_name, schema_name ' Waiting on bqjob_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ... (0s) Current status: DONE +------------------+----------------------------+---------------------+---------------------+-----------------+ | catalog_name | schema_name | creation_time | last_modified_time | location | +------------------+----------------------------+---------------------+---------------------+-----------------+ | cm-xxxxxxxxxxxxx | bq_cm_dataset_by_python2nd | 2020-02-25 14:43:57 | 2020-02-25 14:43:57 | asia-east1 | | cm-xxxxxxxxxxxxx | bq_dataset_by_cli | 2020-03-11 07:11:52 | 2020-03-11 07:11:52 | asia-northeast2 | | cm-xxxxxxxxxxxxx | bq_dataset_bycli2 | 2020-03-11 06:41:08 | 2020-03-11 06:41:08 | asia-northeast2 | | cm-xxxxxxxxxxxxx | cmbqdataset | 2020-02-25 08:42:25 | 2020-03-11 22:33:09 | asia-northeast1 | +------------------+----------------------------+---------------------+---------------------+-----------------+
INFORMATION_SCHEMA.SCHEMATA_OPTIONSビューの内容には現在のユーザーがアクセスできるプロジェクトの各データセットの情報が入っています。こちらもクエリ一発でデータが取ってこれるのでお手軽感がありますね。
$ bq query --nouse_legacy_sql '
SELECT
project_name,
dataset_name,
option_name,
option_type,
option_value
FROM
(SELECT
catalog_name AS project_name,
schema_name AS dataset_name,
option_name,
option_type,
option_value
FROM
INFORMATION_SCHEMA.SCHEMATA_OPTIONS
)
ORDER BY
project_name,
dataset_name
'
Waiting on bqjob_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1 ... (1s) Current status: DONE
+------------------+----------------------------+-------------------------------+-------------------------------+-----------------------------------------------------------+
| project_name | dataset_name | option_name | option_type | option_value |
+------------------+----------------------------+-------------------------------+-------------------------------+-----------------------------------------------------------+
| cm-xxxxxxxxxxxxx | bq_cm_dataset_by_python2nd | default_table_expiration_days | FLOAT64 | 0.041666666666666664 |
| cm-xxxxxxxxxxxxx | bq_cm_dataset_by_python2nd | description | STRING | "Python経由で作られたデータセットです." |
| cm-xxxxxxxxxxxxx | bq_dataset_by_cli | default_table_expiration_days | FLOAT64 | 92.5925925925926 |
| cm-xxxxxxxxxxxxx | bq_dataset_by_cli | description | STRING | "CLI経由で作成したデータセットです" |
| cm-xxxxxxxxxxxxx | bq_dataset_bycli2 | default_table_expiration_days | FLOAT64 | 92.5925925925926 |
| cm-xxxxxxxxxxxxx | bq_dataset_bycli2 | description | STRING | "CLI経由で作成したデータセットです" |
| cm-xxxxxxxxxxxxx | cmbqdataset | default_table_expiration_days | FLOAT64 | 180.0 |
| cm-xxxxxxxxxxxxx | cmbqdataset | description | STRING | "テスト：ブログ用に作成したBigQueryデータセットです。" |
| cm-xxxxxxxxxxxxx | cmbqdataset | labels | ARRAY<STRUCT<STRING, STRING>> | [STRUCT("env", "develop"), STRUCT("production", "false")] |
+------------------+----------------------------+-------------------------------+-------------------------------+-----------------------------------------------------------+
同じ内容をPythonプログラムからアクセスする場合は、以下のようなコードで実現が可能です。
# TODO(developer): Import the client library.
from google.cloud import bigquery
# TODO(developer): Construct a BigQuery client object.
client = bigquery.Client()
query = """
SELECT
project_name,
dataset_name,
option_name,
option_type,
option_value
FROM
(SELECT
catalog_name AS project_name,
schema_name AS dataset_name,
option_name,
option_type,
option_value
FROM
INFORMATION_SCHEMA.SCHEMATA_OPTIONS
)
ORDER BY
project_name,
dataset_name
"""
query_job = client.query(query) # Make an API request.
print("The query data:")
for row in query_job:
# Row values can be accessed by field name or index.
#print("name={}, count={}".format(row[0], row["total_people"]))
print(row)
出力結果：
$ python exec-bigquery-sql.py
The query data:
Row(('cm-xxxxxxxxxxxxx', 'bq_cm_dataset_by_python2nd', 'default_table_expiration_days', 'FLOAT64', '0.041666666666666664'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'bq_cm_dataset_by_python2nd', 'description', 'STRING', '"Python経由で作られたデータセットです."'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'bq_dataset_by_cli', 'default_table_expiration_days', 'FLOAT64', '92.5925925925926'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'bq_dataset_by_cli', 'description', 'STRING', '"CLI経由で作成したデータセットです"'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'bq_dataset_bycli2', 'default_table_expiration_days', 'FLOAT64', '92.5925925925926'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'bq_dataset_bycli2', 'description', 'STRING', '"CLI経由で作成したデータセットです"'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'cmbqdataset', 'default_table_expiration_days', 'FLOAT64', '180.0'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'cmbqdataset', 'description', 'STRING', '"テスト：ブログ用に作成したBigQueryデータセットです。"'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'cmbqdataset', 'labels', 'ARRAY<STRUCT<STRING, STRING>>', '[STRUCT("env", "develop"), STRUCT("production", "false")]'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
まとめ
という訳で、Google BigQueryにおける「データセット」に関する情報を取得する際の実践例についての実践まとめでした。いずれの場合も非常にシンプル且つ簡単に情報が取得出来ていたので安心しました。
